package cn.ebook.jdbc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.ebook.entity.Ebook;
import cn.ebook.jdbc.dao.EbookDao;
import cn.ebook.util.BaseUtil;

public class EbookImpl implements EbookDao {
	/**
	 * 实现图书JNDI接口
	 * 
	 * @author 洋洋洋.
	 * @date 2018-7-25 7:23
	 * 
	 */
	// 连接数据库实例
	private Connection connection;
	// 预执行sql的实例
	private PreparedStatement ps;
	// 结果集实例
	private ResultSet result;

	/**
	 * 实现EbookDao通过图书类型和分页查询图书的方法
	 * 
	 * @author 洋洋洋.
	 * @date 2018-7-25 7:26
	 */
	public List<Ebook> findEbookByCategoryByLimit(Long categoryId, Integer firstIndex, Integer pageSize)
			throws Exception {
		List<Ebook> list = new ArrayList<Ebook>();
		// 封装sql
		String sql = "SELECT id ,category_id ,title ,summary,upload_user, create_date FROM ebook ";
		if (categoryId != null && categoryId != 0L) {
			sql += "where category_id=? ";
		}
		sql += "limit ?,?";
		// 获取连接对象
		connection = BaseUtil.getConnection();
		// 预执行sql
		ps = connection.prepareStatement(sql);
		// 填补占位符
		if (categoryId != null && categoryId != 0L) {
			ps.setObject(1, categoryId);
			ps.setObject(2, firstIndex);
			ps.setObject(3, pageSize);
		} else {
			ps.setObject(1, firstIndex);
			ps.setObject(2, pageSize);
		}

		// 执行sql返回结果集
		result = ps.executeQuery();

		// 遍历结果集
		while (result.next()) {
			Ebook e = new Ebook();
			e.setId(result.getLong("id"));
			e.setCategoryId(result.getLong("category_id"));
			e.setTitle(result.getString("title"));
			e.setSummary(result.getString("summary"));
			e.setUploadUser(result.getString("upload_user"));
			e.setCreateDate(result.getDate("create_date"));
			list.add(e);
		}
		// 释放资源
		BaseUtil.closeAll(result, connection, ps);
		// 返回结果
		return list;
	}

	/**
	 * 查询图书条数
	 * 
	 * @author 洋洋洋.
	 * @date 2018-7-27 5:36.
	 * 
	 */
	public int countBooks(Long categoryId) throws Exception {
		// 封装sql
		String sql = "SELECT COUNT(1) FROM ebook ";
		if (categoryId != null && categoryId != 0L) {
			sql += "WHERE category_id=?";
		}
		// 获取连接
		connection = BaseUtil.getConnection();
		// 预执行sql
		ps = connection.prepareStatement(sql);
		// 填补占位符
		if (categoryId != null && categoryId != 0L) {
			ps.setObject(1, categoryId);
		}
		// 执行sql返回结果
		result = ps.executeQuery();
		// 遍历结果
		int totalData = 0;
		while (result.next()) {
			totalData = result.getInt("count(1)");
		}
		// 返回结果
		return totalData;
	}

	/**
	 * 修改某条图书
	 * 
	 */
	public Integer update(Ebook ebook) throws Exception {
		// 封装sql
		String sql = "UPDATE ebook SET category_id =?,title=?,summary=?,upload_user=?,create_date=? WHERE id=?;";
		// 填占位符
		Object[] obj = { ebook.getCategoryId(), ebook.getTitle(), ebook.getSummary(), ebook.getUploadUser(),
				ebook.getCreateDate(), ebook.getId() };
		// 调用通用增删改方法
		int a = BaseUtil.addOrDelOrSet(sql, obj);
		return a;
	}

	/**
	 * 根据图书id查图书
	 * 
	 * @author 洋洋洋.
	 * @date 2018-7-27 6:31.
	 * @ebookId 图书id
	 * @return 某本图书
	 */
	public Ebook findEbookByid(Long ebookId) throws Exception {
		// 封装sql
		String sql = "SELECT id ,category_id ,title ,summary,upload_user ,create_date ,create_date FROM ebook ";
		if (ebookId != 0L || ebookId != null) {
			sql += "WHERE id=?";
		}
		// 获取连接
		connection = BaseUtil.getConnection();
		// 预执行sql
		ps = connection.prepareStatement(sql);
		// 填补占位符
		if (ebookId != 0L || ebookId != null) {
			ps.setObject(1, ebookId);
		}
		// 执行sql返回结果
		result = ps.executeQuery();
		// 遍历结果
		Ebook e = new Ebook();
		while (result.next()) {
			e.setId(result.getLong("id"));
			e.setCategoryId(result.getLong("category_id"));
			e.setTitle(result.getString("title"));
			e.setSummary(result.getString("summary"));
			e.setUploadUser(result.getString("upload_user"));
			e.setCreateDate(result.getDate("create_date"));
		}
		// 返回结果
		return e;
	}

	/**
	 * 添加图书
	 * 
	 * @author 洋洋洋.
	 * @date 2018-7-27 7:01.
	 */
	public Integer add(Ebook ebook) throws Exception {
		// 封装sql
		String sql = "INSERT INTO ebook(category_id,title,summary,upload_user,create_date)VALUES(?,?,?,?,?);";
		// 填占位符
		Object[] obj = { ebook.getCategoryId(), ebook.getTitle(), ebook.getSummary(), ebook.getUploadUser(),
				ebook.getCreateDate() };
		// 调用通用增删改方法
		int a = BaseUtil.addOrDelOrSet(sql, obj);
		return a;
	}

	/**
	 * 删除某条图书
	 * 
	 * @author 洋洋洋.
	 * @throws SQLException
	 * @date 2018-7-27 7:09.
	 */
	public Integer del(Long id) throws SQLException {
		// 封装sql
		String sql = "DELETE FROM ebook WHERE id=?;";
		// 填占位符
		Object obj = id;
		// 调用通用增删改方法
		int a = BaseUtil.addOrDelOrSet(sql, obj);
		return a;
	}

}
